package ru.osu.acm.common.industrial;

import ru.osu.acm.common.entities.Person;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * User: aleksey
 * Date: Sep 22, 2009
 * Time: 12:30:38 PM
 */
public class PersonFactory extends Factory {
    public static Person getPerson(int personId, Connection con, boolean closeConnection) {
        Person person = null;

        try {
            PreparedStatement statement = con.prepareStatement("select * from person where id = ?");
            statement.setInt(1, personId);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                person = new Person(
                        resultSet.getInt("id"),
                        resultSet.getString("login").trim(),
                        resultSet.getString("pass").trim(),
                        resultSet.getString("firstname").trim(),
                        resultSet.getString("secondname").trim(),
                        resultSet.getBoolean("isadmin"));
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return person;
    }

    public static ArrayList<Person> getAllPersons(Connection con, boolean closeConnection) {
        ArrayList<Person> persons = new ArrayList<Person>();

        try {
            PreparedStatement statement = con.prepareStatement("select * from person order by id");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                persons.add(new Person(
                        resultSet.getInt("id"),
                        resultSet.getString("login").trim(),
                        resultSet.getString("pass").trim(),
                        resultSet.getString("firstname").trim(),
                        resultSet.getString("secondname").trim(),
                        resultSet.getBoolean("isadmin")));
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return persons;
    }

    public static ArrayList<Person> getContestPersons(int contestId, Connection con, boolean closeConnection) {
        ArrayList<Person> persons = new ArrayList<Person>();

        try {
            PreparedStatement statement = con.prepareStatement("select * from contestperson where contest = ? order by id");
            statement.setInt(1, contestId);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Person person = getPerson(resultSet.getInt("person"), con, false);
                persons.add(person);
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return persons;
    }

    public static void removePersonFromContest(int personId, int contestId, Connection con, boolean closeConnection) {
        try {
            PreparedStatement statement = con.prepareStatement("delete from contestperson where person = ? and contest = ?");
            statement.setInt(1, personId);
            statement.setInt(2, contestId);
            statement.executeUpdate();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
    }

    public static void addPersonToContest(int personId, int contestId, Connection con, boolean closeConnection) {
        boolean alreadyAdded = false;

        try {
            PreparedStatement statement = con.prepareStatement("select * from contestperson where contest = ? and person = ?");
            statement.setInt(1, contestId);
            statement.setInt(2, personId);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                alreadyAdded = true;
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        if (alreadyAdded) {
            closeConnection(con, closeConnection);
            return;
        }

        try {
            int newId = getNextId("contestperson", con, false);
            PreparedStatement statement = con.prepareStatement("insert into contestperson(id, person, contest) values (?, ?, ?)");
            statement.setInt(1, newId);
            statement.setInt(2, personId);
            statement.setInt(3, contestId);
            statement.executeUpdate();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
    }
}
